module.exports = {
  /** 用户登录验证 */
  LogonValid: "SELECT * FROM mfstaff WHERE LogonUser=@Account AND PWD=@Password AND UserType in ('0', '1', '6')",

  /** 用户登录验证(单点登录) */
  SLogonValid: "SELECT * FROM mfstaff WHERE TenantGuid=@TenantGuid AND LogonUser=@Account AND UserType='6'",

  /** 仓库查询 */
  WarehouseQuery: `SELECT
        GUID,TenantGUID,CorpGUID,CorpName,WarehouseCorpGUID,WarehouseCorpName,
        LogCenterGUID,LogCenterName,ChineseName,HelpCode,Abbreviation,LogAreaGUID,
        LogAreaName,BizState,Province,City,District,Address,Mananger,SHInfor,
        ALInfo,Einfo,TotalAreaAge,TotalVolume,CanRentedArea,Minleasedrea,WareHouseType,LeasingWay,
        LeasingPrice,Platform,FireFighting,BuildingStructure,FloorHeight,ServiceRange,
        GoodsCategory,SecuritySystem,FireFightingSystem,WarehouseNature,StorageType,
        ISGSP,ISGMP,ISGAP,Contacts,ContactTel,Mailaddr,CreateUserId,CreateUserName,
        UpdateUserId,UpdateUserName,DataVersion,IsDeleted,DataIntegrity,CityAreaName,
        VarietiesCount,PKGS,
        CREATE_TIME,UPDATE_TIME,OutBoundAbility,WarehouseIntroduction,PictContent,Venue,IsPublish
    FROM MFWareHouse`,

  /** 需求查询 */
  RequirementQuery: `SELECT
        GUID,TenantGuid,CorpGuid,DemandServices,Province,City,District,Venue,TotalAreaAge,
        StartRentalDate,DeadLine,PlanLeasePeriod,WareHouseType,LeasingPrice,Platform,
        FireFighting,BuildingStructure,FloorHeight,GoodsCategory,PropertyRightsFlag,
        LandCertificateFlag,HydropowerNetwork,SecuritySystem,FireFightingSystem,
        MaxElectricPower,Contacts,ContactTel,Introduction,StorageType,CreateUserId,
        CreateUserName,UpdateUserId,UpdateUserName,DataVersion,IsDeleted,CREATE_TIME,UPDATE_TIME,IsPublish
    FROM MFWarehouseRentingDemand`,

  /** 新闻查询 */
  NewsQuery: `SELECT 
        r.GUID,r.TenantGuid,r.Title,r.Summary,r.PictContent,r.Content,r.BrowseNum,r.CREATE_TIME,r.UPDATE_TIME,r.IsPublish,
        t.Chinesename, r.UpdateUserID, r.UpdateUserName
      FROM mfnews r inner join mftenant t on r.tenantguid=t.guid`,

  /** 更新新闻浏览量 */
  NewsBrowseNumAdd: 'UPDATE mfnews set BrowseNum=IFNULL(BrowseNum, 0)+1 WHERE Guid=@guid',

  /** 物流中心 */
  LogCenterQuery: `SELECT GUID,TenantGUID,ChineseName FROM MFLogisticsCenter`,

  /** 新增仓库 */
  AddWarehouse: `INSERT INTO mfwarehouse(
        GUID,TenantGUID,CorpGUID,CorpName,WarehouseCorpGUID,WarehouseCorpName,LogCenterGUID,LogCenterName,
        ChineseName,HelpCode,Abbreviation,LogAreaGUID,LogAreaName,BizState,Province,City,District,Address,
        Mananger,SHInfor,ALInfo,Einfo,TotalAreaAge,CanRentedArea,Minleasedrea,WareHouseType,LeasingWay,
        LeasingPrice,Platform,FireFighting,BuildingStructure,FloorHeight,ServiceRange,GoodsCategory,
        SecuritySystem,FireFightingSystem,WarehouseNature,StorageType,ISGSP,ISGMP,ISGAP,Contacts,
        ContactTel,Mailaddr,CreateUserId,CreateUserName,UpdateUserId,UpdateUserName,IsDeleted,CityAreaName,
        CREATE_TIME,UPDATE_TIME,OutBoundAbility,WarehouseIntroduction,PictContent,Venue,IsPublish
      )
      values(
        @GUID,@TenantGUID,@CorpGUID,@CorpName,@WarehouseCorpGUID,@WarehouseCorpName,@LogCenterGUID,@LogCenterName,
        @ChineseName,@HelpCode,@Abbreviation,@LogAreaGUID,@LogAreaName,@BizState,@Province,@City,@District,@Address,
        @Mananger,@SHInfor,@ALInfo,@Einfo,@TotalAreaAge,@CanRentedArea,@Minleasedrea,@WareHouseType,@LeasingWay,
        @LeasingPrice,@Platform,@FireFighting,@BuildingStructure,@FloorHeight,@ServiceRange,@GoodsCategory,
        @SecuritySystem,@FireFightingSystem,@WarehouseNature,@StorageType,@ISGSP,@ISGMP,@ISGAP,@Contacts,
        @ContactTel,@Mailaddr,@CreateUserId,@CreateUserName,@UpdateUserId,@UpdateUserName,'N',@CityAreaName,
        now(),now(),@OutBoundAbility,@WarehouseIntroduction,@PictContent,@Venue,'N'
      )`,

  /** 修改仓库 */
  UpdateWarehouse: `UPDATE mfwarehouse
      SET
        LogCenterGUID=@LogCenterGUID,
        LogCenterName=@LogCenterName,
        ChineseName=@ChineseName,
        HelpCode=@HelpCode,
        Abbreviation=@Abbreviation,
        BizState=@BizState,
        Province=@Province,
        City=@City,
        District=@District,
        Address=@Address,
        Mananger=@Mananger,
        SHInfor=@SHInfor,
        ALInfo=@ALInfo,
        Einfo=@Einfo,
        TotalAreaAge=@TotalAreaAge,
        CanRentedArea=@CanRentedArea,
        Minleasedrea=@Minleasedrea,
        WareHouseType=@WareHouseType,
        LeasingWay=@LeasingWay,
        LeasingPrice=@LeasingPrice,
        Platform=@Platform,
        FireFighting=@FireFighting,
        BuildingStructure=@BuildingStructure,
        FloorHeight=@FloorHeight,
        ServiceRange=@ServiceRange,
        GoodsCategory=@GoodsCategory,
        SecuritySystem=@SecuritySystem,
        FireFightingSystem=@FireFightingSystem,
        WarehouseNature=@WarehouseNature,
        StorageType=@StorageType,
        Contacts=@Contacts,
        ContactTel=@ContactTel,
        Mailaddr=@Mailaddr,
        UpdateUserId=@UpdateUserId,
        UpdateUserName=@UpdateUserName,
        CityAreaName=@CityAreaName,
        WarehouseIntroduction=@WarehouseIntroduction,
        PictContent=@PictContent,
        DataIntegrity=@DataIntegrity,
        Venue=@Venue
    WHERE GUID=@GUID;`,

  /** 新增需求 */
  AddRequirement: `
    INSERT INTO mfwarehouserentingdemand(
        GUID,TenantGuid,CorpGuid,CargoOwnerGUID,CargoOwnerName,BizState,DemandServices,Province,City,District,
        Venue,Address,TotalAreaAge,StartRentalDate,DeadLine,PlanLeasePeriod,WareHouseType,LeasingPrice,Platform,FireFighting,
        BuildingStructure,FloorHeight,GoodsCategory,PropertyRightsFlag,LandCertificateFlag,HydropowerNetwork,SecuritySystem,FireFightingSystem,MaxElectricPower,Contacts,
        ContactTel,Introduction,StorageType,CREATE_TIME,UPDATE_TIME,CreateUserID,CreateUserName,UpdateUserID,UpdateUserName,IsDeleted,
        IsPublish
    )
    values(
        @GUID,@TenantGuid,@CorpGuid,@CargoOwnerGUID,@CargoOwnerName,@BizState,@DemandServices,@Province,@City,@District,
        @Venue,@Address,@TotalAreaAge,@StartRentalDate,@DeadLine,@PlanLeasePeriod,@WareHouseType,@LeasingPrice,@Platform,@FireFighting,
        @BuildingStructure,@FloorHeight,@GoodsCategory,@PropertyRightsFlag,@LandCertificateFlag,@HydropowerNetwork,@SecuritySystem,@FireFightingSystem,
        @MaxElectricPower,@Contacts,@ContactTel,@Introduction,@StorageType,now(),now(),@CreateUserID,@CreateUserName,@UpdateUserID,@UpdateUserName,
        'N','N')`,

  /** 修改需求 */
  UpdateRequirement: `UPDATE mfwarehouserentingdemand
      SET
        BizState=@BizState,
        DemandServices=@DemandServices,
        Province=@Province,
        City=@City,
        District=@District,
        Venue=@Venue,
        Address=@Address,
        TotalAreaAge=@TotalAreaAge,
        StartRentalDate=@StartRentalDate,
        DeadLine=@DeadLine,
        PlanLeasePeriod=@PlanLeasePeriod,
        WareHouseType=@WareHouseType,
        LeasingPrice=@LeasingPrice,
        Platform=@Platform,
        FireFighting=@FireFighting,
        BuildingStructure=@BuildingStructure,
        FloorHeight=@FloorHeight,
        GoodsCategory=@GoodsCategory,
        PropertyRightsFlag=@PropertyRightsFlag,
        LandCertificateFlag=@LandCertificateFlag,
        HydropowerNetwork=@HydropowerNetwork,
        SecuritySystem=@SecuritySystem,
        FireFightingSystem=@FireFightingSystem,
        MaxElectricPower=@MaxElectricPower,
        Contacts=@Contacts,
        ContactTel=@ContactTel,
        Introduction=@Introduction,
        StorageType=@StorageType,
        UPDATE_TIME=now(),
        UpdateUserID=@UpdateUserID,
        UpdateUserName=@UpdateUserName
    WHERE GUID=@GUID;`,

  /** 新增新闻 */
  AddNews: `INSERT INTO mfnews(
        GUID,TenantGuid,Title,Summary,PictContent,Content,BrowseNum,CREATE_TIME,UPDATE_TIME,CreateUserID,
        CreateUserName,UpdateUserID,UpdateUserName,IsDeleted,IsPublish
    )
    values(
        @GUID,@TenantGuid,@Title,@Summary,@PictContent,@Content,@BrowseNum,now(),now(),@CreateUserID,
        @CreateUserName,@UpdateUserID,@UpdateUserName,'N','N'
    )`,

  /** 修改新闻 */
  UpdateNews: `UPDATE mfnews
      SET
        Title=@Title,
        Summary=@Summary,
        PictContent=@PictContent,
        Content=@Content,
        BrowseNum=@BrowseNum,
        IsPublish=@IsPublish,
        UPDATE_TIME=now(),
        UpdateUserID=@UpdateUserID,
        UpdateUserName=@UpdateUserName
      WHERE GUID=@GUID`,

  /** 发布仓库 */
  PublishWarehouse: `UPDATE mfwarehouse SET IsPublish=@IsPublish WHERE GUID=@Guid`,

  /** 删除仓库信息 */
  RemoveWarehouse: 'delete from mfwarehouse where Guid=@Guid',

  /** 删除需求信息 */
  RemoveRequirement: 'delete from MFWarehouseRentingDemand where Guid=@Guid',

  /** 删除新闻信息 */
  RemoveNews: 'delete from mfnews where Guid=@Guid',

  /** 发布需求 */
  PublishRequirement: `UPDATE mfwarehouserentingdemand SET IsPublish=@IsPublish WHERE GUID=@Guid`,

  /** 发布新闻 */
  PublishNews: `UPDATE mfnews SET IsPublish=@IsPublish WHERE GUID=@Guid`,

  /** 获取仓盟参数 */
  GetAllianceParameters: `SELECT * from mfwhallianceparameter`,

  /** 检查仓盟参数是否存在 */
  CheckAllianceParameterExist: `SELECT Name from mfwhallianceparameter where Name=@name`,

  /** 添加仓盟参数 */
  AddAllianceParameter: `INSERT INTO mfwhallianceparameter (
        GUID,Name,Value,CREATE_TIME,UPDATE_TIME,CreateUserID,
        CreateUserName,UpdateUserID,UpdateUserName,IsDeleted
      ) VALUES (
        @GUID,@Name,@Value,SYSDATE(),SYSDATE(),@CreateUserID,
        @CreateUserName,@UpdateUserID,@UpdateUserName,'N'
      );`,

  /** 更新仓盟参数信息 */
  UpdateAllianceParameter: `UPDATE mfwhallianceparameter SET
      Value=@Value,UPDATE_TIME=SYSDATE(),UpdateUserID=@UpdateUserID,UpdateUserName=@UpdateUserName
      WHERE Name=@Name`,

  /** 添加仓盟访问记录 */
  AddWhAllianceVisitsRecord: `INSERT INTO mfwhalliancevisitsrecord (
    GUID,IP,PageAddress,WareHouseGUID,WareHouseName,DepartureProvince,DepartureCity,
    DepartureDistrict,DepartureVenue,CREATE_TIME,UPDATE_TIME,CreateUserID,CreateUserName,
    UpdateUserID,UpdateUserName,IsDeleted
  )
  VALUES(
    REPLACE(UUID(),'-',''),@IP,@PageAddress,@WareHouseGUID,@WareHouseName,@DepartureProvince,
    @DepartureCity, @DepartureDistrict, @DepartureVenue,SYSDATE(), SYSDATE(),
    @CreateUserID,@CreateUserName,@UpdateUserID,@UpdateUserName,'N'
  )`,
  /** 查询会员信息 */
  SearchTenantTransportData:
    `SELECT * FROM (
      SELECT 
      TA.GUID,TA.ChineseName,TA.HelpCode,TA.EnglishName,TA.Abbreviation,TA.TenantType,TA.BizState,
      TA.SN,TA.Province,TA.City,TA.District,TA.Address,TA.AreaFullText,TA.RegisteredDate,
      TA.RegisteredCapital,TA.PersonInCharge,TA.IdCode,TA.TplFlag,TA.OORange,TA.OrganizationTel,
      TA.Email,TA.Contacts,TA.ContactTel,TA.WebChatOpentID,TA.SentMsgFlag,TA.OrganizationFax,
      TA.Mailaddr,TA.EnterpriseName,TA.Bank,TA.BankAccount,TA.IsLicThreeToOne,TA.PictContent,
      TA.IsOperateTeam,TA.ExpireDate,TA.ConfigRights,TA.ApplySource,TA.CreateUserId,TA.CreateUserName,
      TA.UpdateUserId,TA.UpdateUserName,TA.DataVersion,TA.IsDeleted,TA.TenantGuid,TA.CREATE_TIME,
      TA.UPDATE_TIME,TA.CarUnionFlag,TA.IsAudit,TA.IsAccountOpen,TA.Memo,ta.Classification,
      SUM(TB.TotalVolume) AS TotalVolume,
      SUM(TB.TotalAreaAge) AS TotalAreaAge,
      COUNT(TB.GUID) AS WareHouseNumber
      FROM MFTenant TA  LEFT JOIN MFWareHouse TB ON TA.GUID = TB.TenantGUID AND TB.ISGSP = 'Y'
      GROUP BY 
      TA.GUID,TA.ChineseName,TA.HelpCode,TA.EnglishName,TA.Abbreviation,TA.TenantType,TA.BizState,
      TA.SN,TA.Province,TA.City,TA.District,TA.Address,TA.AreaFullText,TA.RegisteredDate,
      TA.RegisteredCapital,TA.PersonInCharge,TA.IdCode,TA.TplFlag,TA.OORange,TA.OrganizationTel,
      TA.Email,TA.Contacts,TA.ContactTel,TA.WebChatOpentID,TA.SentMsgFlag,TA.OrganizationFax,
      TA.Mailaddr,TA.EnterpriseName,TA.Bank,TA.BankAccount,TA.IsLicThreeToOne,TA.PictContent,
      TA.IsOperateTeam,TA.ExpireDate,TA.ConfigRights,TA.ApplySource,TA.CreateUserId,TA.CreateUserName,
      TA.UpdateUserId,TA.UpdateUserName,TA.DataVersion,TA.IsDeleted,TA.TenantGuid,TA.CREATE_TIME,
      TA.UPDATE_TIME,TA.CarUnionFlag,TA.IsAudit,TA.IsAccountOpen,TA.Memo,ta.Classification) T`
};